package io.renren.modules.sys.controller;

import io.renren.common.utils.PageUtils;
import io.renren.common.utils.R;
import io.renren.common.validator.ValidatorUtils;
import io.renren.modules.sys.entity.ColumnsInfoEntity;
import io.renren.modules.sys.entity.DatabaseinfoEntity;
import io.renren.modules.sys.entity.TableInfoEntity;
import io.renren.modules.sys.service.DatabaseinfoService;
import io.renren.modules.sys.service.TableInfoService;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;


/**
 * 表信息管理
 *
 * @author zhangfn
 * @date 2020-06-05 10:55:20
 */
@RestController
@RequestMapping("sys/tableinfo")
public class TableInfoController {
    @Autowired
    private TableInfoService tableInfoService;
    @Autowired
    private DatabaseinfoService databaseinfoService;

    /**
     * 列表
     */
    @RequestMapping("/list")
    @RequiresPermissions("sys:tableinfo:list")
    public R list(@RequestParam Map<String, Object> params) {

        String connId = (String) params.get("connid");
        PageUtils page = null;
        if (null != connId && connId != "") {
            page = tableInfoService.queryPage(params);
        }

        return R.ok().put("page", page);
    }

    /**
     * 根据数据源ID查询下面的表信息
     *
     * @param dbid
     * @return
     */
    @RequestMapping(value = "/getTableByDbid/{dbid}", method = RequestMethod.GET)
    public R taskInfo(@PathVariable String dbid) {
        List<TableInfoEntity> tables = tableInfoService.getTableByDbid(dbid);

        return R.ok().put("tables", tables);
    }


    /**
     * 信息
     */
    @RequestMapping("/info/{id}")
    @RequiresPermissions("sys:tableinfo:info")
    public R info(@PathVariable("id") Integer id) {
        TableInfoEntity tableInfo = tableInfoService.getById(id);

        return R.ok().put("tableInfo", tableInfo);
    }

    /**
     * 保存
     */
    @RequestMapping("/save")
    @RequiresPermissions("sys:tableinfo:save")
    public R save(@RequestBody TableInfoEntity tableInfo) {
        tableInfoService.save(tableInfo);

        return R.ok();
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    @RequiresPermissions("sys:tableinfo:update")
    public R update(@RequestBody TableInfoEntity tableInfo) {
        ValidatorUtils.validateEntity(tableInfo);
        tableInfoService.updateById(tableInfo);

        return R.ok();
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
    @RequiresPermissions("sys:tableinfo:delete")
    public R delete(@RequestBody Integer[] ids) {
        tableInfoService.removeByIds(Arrays.asList(ids));

        return R.ok();
    }

    /**
     * 更新数据库表数据量
     *
     * @param connid
     * @param tableName
     * @param tableid
     * @return
     */
    @RequestMapping(value = "/upCount", method = RequestMethod.POST)
    public R upCount(@RequestParam(value = "connid") String connid,
                     @RequestParam(value = "tableName") String tableName,
                     @RequestParam(value = "tableid") String tableid) {
        String msg = "";
        DatabaseinfoEntity databaseinfoEntity = null;
        databaseinfoEntity = databaseinfoService.queryById(connid);

        if (null != databaseinfoEntity.getId() || databaseinfoEntity.getId().equals("")) {
            Connection conn = null;
            try {
                Class.forName(databaseinfoEntity.getDrivername());
                conn = DriverManager.getConnection(databaseinfoEntity.getUrl()
                        , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());

                if (null != conn) {
                    tableInfoService.upCount(conn, tableName, tableid);
                } else {
                    msg = "当前表所属数据源连接失败！";
                }

            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            msg = "当前数据源查询为空！";
        }


        return R.ok(msg);
    }

    /**
     * 批量更新数据库表数据量
     *
     * @param connid
     * @param tableName
     * @param tableid
     * @return
     */
    @RequestMapping(value = "/upMoreCount", method = RequestMethod.POST)
    public R upMoreCount(@RequestBody Integer[] ids) {
        String msg = "";
        for (int id : ids) {
            TableInfoEntity tableInfoEntity = tableInfoService.getById(id);

            DatabaseinfoEntity databaseinfoEntity = null;
            databaseinfoEntity = databaseinfoService.queryById(tableInfoEntity.getDbId());

            if (null != databaseinfoEntity.getId() || databaseinfoEntity.getId().equals("")) {
                Connection conn = null;
                try {
                    Class.forName(databaseinfoEntity.getDrivername());
                    conn = DriverManager.getConnection(databaseinfoEntity.getUrl()
                            , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());

                    if (null != conn) {
                        tableInfoService.upCount(conn, tableInfoEntity.getTableName(), String.valueOf(id));
                    } else {
                        msg = "当前表所属数据源连接失败！";
                    }

                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                msg = "当前数据源查询为空！";
            }
        }
        return R.ok(msg);
    }

    /**
     * 查询表列信息
     *
     * @param map
     * @return
     */
    @RequestMapping(value = "/queryclomuns", method = RequestMethod.POST)
    public R queryclomuns(@RequestBody HashMap<String, String> map) {
        Connection conn = null;
        List<ColumnsInfoEntity> columns = new ArrayList<>();
        HashMap map1 = new HashMap();
        String connid = map.get("connid");
        String tableName = map.get("tableName");
        String tableId = map.get("tableId");

        DatabaseinfoEntity databaseinfoEntity = new DatabaseinfoEntity();
        if (null != connid && !connid.equals("")) {
            databaseinfoEntity = databaseinfoService.queryById(connid);

            if (null != databaseinfoEntity.getConnname() && !databaseinfoEntity.getConnname().equals(null)) {

                try {
                    Class.forName(databaseinfoEntity.getDrivername());
                    conn = DriverManager.getConnection(databaseinfoEntity.getUrl()
                            , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());

                    switch (databaseinfoEntity.getDatasourcetype()) {
                        case "mysql":
                            map1 = tableInfoService.mysqlQueryColumns(conn, tableId, tableName);
                            if (map1.size() > 0) {
                                //异步更新填充率

                                Connection connTcl = DriverManager.getConnection(databaseinfoEntity.getUrl()
                                        , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());
                                tableInfoService.upTcl("mysql", connTcl, tableId, tableName, map1);
                            }
                            break;
                        case "postgresql":
                            map1 = tableInfoService.pgsqlQueryColumns(conn, tableId, tableName);
                            //异步更新填充率

                            Connection connTcl = DriverManager.getConnection(databaseinfoEntity.getUrl()
                                    , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());
                            tableInfoService.upTcl("postgresql", connTcl, tableId, tableName, map1);
                            break;
                        case "oracle":
                            map1 = tableInfoService.oracleQueryColumns(conn, tableId, tableName);
                            //异步更新填充率

                            Connection connTcl1 = DriverManager.getConnection(databaseinfoEntity.getUrl()
                                    , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());
                            tableInfoService.upTcl("oracle", connTcl1, tableId, tableName, map1);
                            break;
                        default:
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                return R.ok().put("columns", map1);
            }
        }
        return R.ok();

    }

    /**
     * 批量查询表列信息
     *
     * @param map
     * @return
     */
    @RequestMapping(value = "/queryMoreClomuns", method = RequestMethod.POST)
    public R queryMoreClomuns(@RequestBody Integer[] ids) {

        for (int id : ids) {
            TableInfoEntity tableInfo = tableInfoService.getById(id);

            Connection conn = null;
            List<ColumnsInfoEntity> columns = new ArrayList<>();
            HashMap map1 = new HashMap();
            String connid = tableInfo.getDbId();
            String tableName = tableInfo.getTableName();
            String tableId = String.valueOf(tableInfo.getId());

            DatabaseinfoEntity databaseinfoEntity = new DatabaseinfoEntity();
            if (null != connid && !connid.equals("")) {
                databaseinfoEntity = databaseinfoService.queryById(connid);

                if (null != databaseinfoEntity.getConnname() && !databaseinfoEntity.getConnname().equals(null)) {

                    try {
                        Class.forName(databaseinfoEntity.getDrivername());
                        conn = DriverManager.getConnection(databaseinfoEntity.getUrl()
                                , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());

                        switch (databaseinfoEntity.getDatasourcetype()) {
                            case "mysql":
                                map1 = tableInfoService.mysqlQueryColumns(conn, tableId, tableName);
                                if (map1.size() > 0) {
                                    //异步更新填充率

                                    Connection connTcl = DriverManager.getConnection(databaseinfoEntity.getUrl()
                                            , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());
                                    tableInfoService.upTcl("mysql", connTcl, tableId, tableName, map1);
                                }
                                break;
                            case "postgresql":
                                map1 = tableInfoService.pgsqlQueryColumns(conn, tableId, tableName);
                                Connection connTcl = DriverManager.getConnection(databaseinfoEntity.getUrl()
                                        , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());
                                tableInfoService.upTcl("postgresql", connTcl, tableId, tableName, map1);
                                break;
                            case "oracle":
                                map1 = tableInfoService.oracleQueryColumns(conn, tableId, tableName);
                                Connection conntcl = DriverManager.getConnection(databaseinfoEntity.getUrl()
                                        , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());
                                tableInfoService.upTcl("oracle", conntcl, tableId, tableName, map1);
                                break;
                            default:
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
        return R.ok();

    }
}
